
alter function fn_rma_for_vertical_report
(
 @product_Id int,
 @customer_Id varchar(20),
 @quarter varchar(10),
 @year  varchar(10),
 @rstatus varchar(20),
 @category varchar(15),
 @CURRENT_DATE datetime
)
returns @fn_results Table(Amount float, Status varchar(20), orderDate datetime)
as
begin
  
declare @results table(Amount float, Status varchar(20), orderDate datetime) 

declare @customer_name varchar(65)
declare @amount float
declare @schedule_date datetime
declare @unit_price float
declare @part_number varchar(50)

declare @lineItemStatus varchar(15)
declare @lineItemCloseDate datetime

declare @month varchar(10)
declare @date_From varchar(10)
declare @date_To varchar(10)
declare @dateDiff datetime

if @rstatus = 'all'
begin
	set @rstatus = '0'
end
set @month = '1'
  set @date_From = CONVERT(varchar,CONVERT(datetime,@month+'/01/'+@year),101)
  set @date_To   = CONVERT(varchar,DATEADD(month,12,CONVERT(datetime,@date_From)),101)
  set @dateDiff  = CONVERT(datetime,DATEDIFF(day,1,@date_To),101)
  set @date_To   = CONVERT(varchar,@dateDiff,101)
 
declare @RmaItemCursor cursor
 
if (@product_Id <> '0' and @customer_Id <> '0'  )
begin
	set @RmaItemCursor = cursor for
 	select Customer_Name , RLIT.Part_Number , RMA_Status , RMA_Requested_Date , RT.Close_Date , Quantity , Unit_Price, Is_Credit , RLIT.status , RLIT.Close_Date
 	from RMA_Table RT, RMA_Line_Item_Table RLIT, Customer CUST, Product PRO, Process_Step PS
 	where 	
		RT.RMA_ID = RLIT.RMA_ID 
 		and RMA_Status <> 'CANCELLED'
 		and PRO.Part_Number = RLIT.Part_Number
 		and PRO.ProcessStep_ID = PS.ProcessStep_ID
 		and PS.PS_Type_Name = 'Finished Goods'
 		and PRO.Product_ID = @product_Id
 		and RT.Customer_Name = CUST.C_Name
 		and CUST.Customer_ID = @customer_Id
end
else if (@product_Id <> '0' and @customer_Id = '0'  )
begin	
	set @RmaItemCursor = cursor for
	select Customer_Name , RLIT.Part_Number , RMA_Status , RMA_Requested_Date , RT.Close_Date , Quantity , Unit_Price, Is_Credit , RLIT.status , RLIT.Close_Date
	from RMA_Table RT, RMA_Line_Item_Table RLIT, Customer CUST, Product PRO, Process_Step PS
	where 	
		RT.RMA_ID = RLIT.RMA_ID 
 		and RMA_Status <> 'CANCELLED'
 		and RT.Customer_Name = CUST.C_Name 
 		and RLIT.Part_Number = PRO.Part_Number
 		and PRO.ProcessStep_ID = PS.ProcessStep_ID
 		and PS.PS_Type_Name = 'Finished Goods'
 		and PRO.Product_ID = @product_Id
end		

else if (@product_Id = '0' and @customer_Id <> '0'  )
begin
	set @RmaItemCursor = cursor for
	select Customer_Name , RLIT.Part_Number , RMA_Status , RMA_Requested_Date , RT.Close_Date , Quantity , Unit_Price, Is_Credit , RLIT.status , RLIT.Close_Date
	from RMA_Table RT, RMA_Line_Item_Table RLIT, Customer CUST, Product PRO, Process_Step PS
	where 	
		RT.RMA_ID = RLIT.RMA_ID 
		and RMA_Status <> 'CANCELLED'
		and RLIT.Part_Number = PRO.Part_Number
		and PRO.ProcessStep_ID = PS.ProcessStep_ID
		and PS.PS_Type_Name = 'Finished Goods'
		and RT.Customer_Name = CUST.C_Name
		and CUST.Customer_ID = @customer_Id
end		
else if (@product_Id = '0' and @customer_Id = '0'  )
begin
	set @RmaItemCursor = cursor for
	select Customer_Name , RLIT.Part_Number , RMA_Status , RMA_Requested_Date , RT.Close_Date , Quantity , Unit_Price, Is_Credit , RLIT.status , RLIT.Close_Date
	from RMA_Table RT, RMA_Line_Item_Table RLIT, Customer CUST, Product PRO, Process_Step PS
	where 	
		RT.RMA_ID = RLIT.RMA_ID 
		and RMA_Status <> 'CANCELLED'
		and RT.Customer_Name = CUST.C_Name 
		and RLIT.Part_Number = PRO.Part_Number
		and PRO.ProcessStep_ID = PS.ProcessStep_ID
		and PS.PS_Type_Name = 'Finished Goods'
end
 		
declare @qty int
declare @RMA_Requested_Date datetime
declare @RMA_Close_Date     datetime
declare @RMAStatus varchar(20)
declare @status varchar(20)

declare @Is_Credit int
SET @amount = 0
SET @Is_Credit = 1
set @status = '0'
 
open @RmaItemCursor
fetch next from @RmaItemCursor into @customer_Name , @part_Number , @RMAstatus , @RMA_Requested_Date, @RMA_Close_Date , @qty , @Unit_Price, @Is_Credit , @lineItemStatus , @lineItemCloseDate
	while @@fetch_status = 0
	begin
 		
		--Commented by Gandhi on Jan 5th 2005
		/*if ( @lineItemStatus = 'CLOSE' )
 		begin
 			set @status = 'CLOSE'
 			set @RMA_Close_Date = @lineItemCloseDate
 		end
 
  		if (@RMAstatus = 'ISSUED')
 		begin
 			set @schedule_Date = @RMA_requested_date--CONVERT(datetime,CURRENT_TIMESTAMP)
 			set @status = 'APPROVED'

 		end
 		else if (@RMAstatus = 'CLOSE')
 		begin
 			set @schedule_Date = @RMA_Close_Date
 			set @status = 'SHIPPED'
 		end*/

		--Added by Gandhi on Jan 5th 2005  
   		if (@RMAstatus = 'ISSUED')
  		begin
  			set @schedule_Date = @CURRENT_DATE  --@RMA_requested_date 
			set @status = 'APPROVED'
	
			if ( @lineItemStatus = 'CLOSE' )
  			begin
  				set @status = 'SHIPPED'
  				set @schedule_Date = @lineItemCloseDate
	  		end
   		end
  		else if (@RMAstatus = 'CLOSE')
  		begin
  			
			if (@lineItemCloseDate is not null) 			
			begin
				set @schedule_Date = @lineItemCloseDate	
			end
			else
			begin
				set @schedule_Date = @RMA_Close_Date	
			end

  			set @status = 'SHIPPED'
  		end
		-- Ends here - Gandhi
 
 		IF( @Is_Credit = 1)
 		BEGIN
 			SET @amount = 0
 		END
 		ELSE IF (@Is_Credit = 0)
 		BEGIN
 			SET @amount = @qty * @unit_price 
 		END
 		
 
 		insert into @results(Amount,Status,orderDate) values(@amount,@status,@schedule_Date) 		
 		
 		SET @amount = 0
 		SET @Is_Credit = 1
		set @status = '0'
		
 		set @customer_Name = null
 		set @part_Number = null
 		set @RMAstatus = null
		set @RMA_Requested_Date = null
 		set @RMA_Close_Date = null
 		set @qty = null
 		set @Unit_Price = null
 		set @Is_Credit = null
 
 		fetch next from @RmaItemCursor into @customer_Name , @part_Number , @RMAstatus , @RMA_Requested_Date, @RMA_Close_Date , @qty , @Unit_Price, @Is_Credit , @lineItemStatus , @lineItemCloseDate
 	end	
 		
close @RmaItemCursor
deallocate @RmaItemCursor

if  @rstatus <> '0' 
begin
 		
	if (@Category = 'REQUESTED')
	begin
		insert @fn_results select * from @results where status = @rstatus --order by Req_Date
		return
 	end
 	else if (@Category = 'SCHEDULE')
 	begin
 		insert @fn_results select * from @results where status = @rstatus --order by Schd_Date
 		return
 	end
end
else 
begin
	if (@Category = 'REQUESTED')
	begin
		insert @fn_results select * from @results --order by Req_Date
		return
	end
	else if (@Category = 'SCHEDULE')
	begin
		insert @fn_results select * from @results --order by Schd_Date
 		return
 	end
end

return 
end


